package cnki.kg.demo;

import cnki.kg.demo.entity.QuestionTemplate;
import cnki.kg.demo.entity.TemplateConfig;
import com.alibaba.fastjson.JSON;
import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


@SpringBootTest
@WebAppConfiguration
public class CreateTemplate {

    @Autowired
    @Qualifier("mysqlJdbcTemplate")
    JdbcTemplate mysqlJdbcTemplate;
    @Test
    public void instertQuerstionTemplate() {
        String json="[{\"field\":\"CD\",\"alia\":\"车道\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"SJSU\",\"alia\":\"设计速度\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"ZTLJ\",\"alia\":\"整体路基\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"ZHXJ\",\"alia\":\"载荷新建\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"ZZZBL\",\"alia\":\"资本金占总投资比例\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"YZZBL\",\"alia\":\"银行贷款占总投资比例\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"ZXLMKD\",\"alia\":\"主线路面宽度\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"ZXLMHD\",\"alia\":\"主线路面长度\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"SJDW\",\"alia\":\"设计单位\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"XMYZ\",\"alia\":\"项目业主\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"JFDW\",\"alia\":\"甲方单位\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"TZMS\",\"alia\":\"投资模式\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"JSZT\",\"alia\":\"建设状态\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"起点\",\"alia\":\"起点\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"终点\",\"alia\":\"终点\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"主线路面类型\",\"alia\":\"主线路面类型\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"主线路面类型\",\"alia\":\"主线面层厚度\",\"table\":\"XMJBXX\",\"tablename\":\"项目基本信息\",\"tableid\":\"2187\"},{\"field\":\"XMFR\",\"alia\":\"项目法人\",\"table\":\"BDJBXX\",\"tablename\":\"标段基本信息\",\"tableid\":\"2186\"},{\"field\":\"YFDW\",\"alia\":\"乙方单位\",\"table\":\"BDJBXX\",\"tablename\":\"标段基本信息\",\"tableid\":\"2186\"},{\"field\":\"SGDW\",\"alia\":\"施工单位\",\"table\":\"BDJBXX\",\"tablename\":\"标段基本信息\",\"tableid\":\"2186\"},{\"field\":\"JFDW\",\"alia\":\"甲方单位\",\"table\":\"BDBGHZ\",\"tablename\":\"标段变更汇总\",\"tableid\":\"2499\"},{\"field\":\"JLDW\",\"alia\":\"监理单位\",\"table\":\"BDBGHZ\",\"tablename\":\"标段变更汇总\",\"tableid\":\"2499\"},{\"field\":\"GKPFDW\",\"alia\":\"工可批复单位\",\"table\":\"XMBGHZ\",\"tablename\":\"项目变更汇总\",\"tableid\":\"2185\"},{\"field\":\"BGDJ\",\"alia\":\"变更等级\",\"table\":\"SSJDSJ\",\"tablename\":\"实施阶段数据\",\"tableid\":\"2177\"}]";
        List<Map<String,String>> indicators= JSON.parseObject(json,List.class);
        for (Map<String, String> indicator : indicators) {
            String field=indicator.get("field");
            String alia=indicator.get("alia");
            String table=indicator.get("table");
            String tablename=indicator.get("tablename");
            String tableid=indicator.get("tableid");
            System.out.println(table+"__"+alia+"__"+field);
            String regular = String.format("%s(有|共有|都有)(几种|谁|几类|哪些|哪几个|哪几种|几个|几家|多少家|多少个)",alia);
            String question = String.format("%s有哪些",alia);
            String instr = String.format("insert into QuestionTemplate(IntentionDomainID,IntentionID,Question,Template,Status,CreateUser,CreateTime,ModifyUser,ModifyTime,tableName,tableAlia) " +
                    "values(1075,0,'%s','%s',1,'sa',now(),'sa',now(),'%s','%s')", question,regular,table,tablename);
            KeyHolder keyHolder = new GeneratedKeyHolder();
            mysqlJdbcTemplate.update( new PreparedStatementCreator(){
                                     @Override
                                     public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                                         PreparedStatement ps = conn.prepareStatement(instr, Statement.RETURN_GENERATED_KEYS);
                                         return ps;
                                     }
                                 },
                    keyHolder);
            Integer id=keyHolder.getKey().intValue();
            QuestionTemplate tem = new QuestionTemplate();
            tem.setQuestion(question);
            tem.setRegular(regular);
            tem.setTablecode(table);
            tem.setTableid(Integer.parseInt(tableid));
            tem.setTablename(tablename);
            TemplateConfig config = new TemplateConfig();
            config.setDisplayname(alia);
            config.setFieldalia(alia);
            config.setFiledtype(1);//输出字段
            config.setFieldname(field);
            config.setOperate("agg");
            List<TemplateConfig> configs = new ArrayList<>();
            configs.add(config);
            tem.setTemplateid(id);
            tem.setTemplateconfigs(configs);
            String jsonTemplate = JSON.toJSONString(tem);
            String updatesql=String.format("update QuestionTemplate set JsonTemplate='%s' where QuestionTemplateID=%s",jsonTemplate,id);
            mysqlJdbcTemplate.execute(updatesql);
        }
    }

}
